Find the employee with the highest salary per department.
Output the department name, employee's first name along with the corresponding salary.
Solution:
WITH cte AS
(
SELECT department, first_name, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
FROM employee
)
SELECT department, first_name, salary
FROM cte
WHERE rnk = 1
select first_name, salary,department from employee
where salary in (select max(salary) over(partition by department) from employee)
Query Cost